-- **************************************************************************
--    Project Name:   操作平台-转运中心综合指标数据报表-月报
--    Job Name:       jms_dwm.dwm_ops_center_kpi_summary_mth_dt
--    Author :        季修魁
--    date：          2023/12/06
-- **************************************************************************
-- **************************************************************************
 
set spark.sql.crossJoin.enabled=true;

----车件分离异常次数

with separated_abnormal_tab as (
select network_code
       ,network_name
	     ,trunc(STAT_DATE,'MM')                      as STAT_DATE
       ,sum(cjfl_separated_cnt)                as cjfl_separated_cnt   
       ,sum(cjfl_separated_abnormal_cnt)       as cjfl_separated_abnormal_cnt
  from 
(
select a.*
       ,case when cjfl_separated_cnt>2000 or (cjfl_separated_cnt/sum(cjfl_separated_cnt) over()*100) >=2 then 1 else 0 end as cjfl_separated_abnormal_cnt
  from 
(
SELECT STAT_DATE,NETWORK_CODE ,NETWORK_NAME
       ,SUM(KPI_FZ)         AS CJFL_SEPARATED_CNT
  FROM JMS_DWM.DWM_ANALYSIS_TARGET_NETWORK_SUM_DAY_DT
 WHERE DT>=trunc(add_months('{{ execution_date | cst_ds }}',-1),'MM')  AND DT<='{{ execution_date | cst_ds }}' ---上月+当月
   AND NETWORK_TYPE='4' and KPI_CODE='bis00000154'
 GROUP BY STAT_DATE,NETWORK_CODE,NETWORK_NAME
 ) a 
 ) t2
group by NETWORK_CODE,NETWORK_NAME,trunc(STAT_DATE,'MM')
),

---2.操作效能
opt_efficiency_tab as (
select 
    trunc(happen_date,'MM') as m_day_dt
   ,day(last_day(add_months('{{ execution_date | cst_ds }}',-1))) as mth_day
   ,base.center_code
   ,sum(base.operate_num)     as operate_num
   ,sum(base.self_attendance) as self_attendance
   ,sum(base.hourly_workers)  as hourly_workers
   ,sum(base.epiboly_num)     as epiboly_num
   ,sum(base.operate_num) / (sum(self_attendance)+sum(hourly_workers)+sum(epiboly_num)) / max(czxn_target)  as czxn_eff_reach_rate
from jms_dim.dim_ep_per_efficiency_dt base
left join (
    select czxn_target
    from jms_dim.dim_center_indicator_config_base_dt 
    where dt='{{ execution_date | cst_ds }}' 
    and status='Y'
) dim on 1 = 1 
where base.dt='{{ execution_date | cst_ds }}'
and base.happen_month=substr(add_months('{{ execution_date | cst_ds }}',-1),1,7)
group by trunc(base.happen_date,'MM'),base.center_code
 )
---3.

insert overwrite table jms_dwm.dwm_ops_center_kpi_summary_mth_dt partition (dt)
select a.stat_date
       ,a.center_code
       ,a.center_name
       ,c.agent_code
       ,c.agent_name
       ,a.jgzy_ovtime_cnt
       ,a.jgzy_intime_cnt
       ,a.jgzy_intime_rate
       ,a.cgzy_ovtime_cnt
       ,a.cgzy_intime_cnt
       ,a.cgzy_intime_rate
       ,a.ks_comp_cnt
       ,a.ks_comp_rate
       ,a.ys_loss_cnt
       ,a.ys_loss_rate
       ,a.ps_worn_cnt
       ,a.ps_worn_rate
       ,a.zx_opt_cnt
       ,a.zcbgf_load_nostandard_cnt
       ,a.zz_load_cnt
       ,a.hz_load_cnt
       ,a.zz_load_kg
       ,a.hz_load_kg
       ,a.gx_load_cnt_rate
       ,a.gx_load_kg_rate
       ,a.gx_load_rate
       ,a.cjfl_separated_cnt
       ,a.cjfl_separated_all_cnt
       ,a.cjfl_separated_rate
       ,a.cjfl_separated_abnormal_cnt
       ,a.czxn_eff_trans_cnt
       ,a.czxn_eff_attend_num
       ,a.czxn_eff_reach_rate
       ,a.dp_per_cost_reach_rate
       ,round(case when jgzy_intime_rate<=jgzy_worst then 0 
             when jgzy_intime_rate>jgzy_worst and jgzy_intime_rate<jgzy_target 
			 then if(((jgzy_intime_rate-jgzy_worst) / (jgzy_target-jgzy_worst) * 100)>100,100,((jgzy_intime_rate-jgzy_worst) / (jgzy_target-jgzy_worst) * 100))
             when jgzy_intime_rate=jgzy_target then 100
             when jgzy_intime_rate>jgzy_target and jgzy_target<jgzy_challenge 
			 then if(((jgzy_intime_rate-jgzy_target) * (120-100) / (jgzy_challenge-jgzy_target) +100)>120,120,(jgzy_intime_rate-jgzy_target) * (120-100) / (jgzy_challenge-jgzy_target) +100)
             when jgzy_intime_rate>=jgzy_challenge then 120
        end,2)   as jgzy_intime_score
       ,jgzy_weights        as jgzy_intime_weight
       ,round(case when cgzy_intime_rate<=cgzy_worst then 0 
             when cgzy_intime_rate>cgzy_worst and cgzy_intime_rate<cgzy_target 
			 then if(((cgzy_intime_rate-cgzy_worst) / (cgzy_target-cgzy_worst) * 100)>100,100,((cgzy_intime_rate-cgzy_worst) / (cgzy_target-cgzy_worst) * 100))
             when cgzy_intime_rate=cgzy_target then 100
             when cgzy_intime_rate>cgzy_target and cgzy_target<cgzy_challenge 
			 then if(((cgzy_intime_rate-cgzy_target) * (120-100) / (cgzy_challenge-cgzy_target) +100)>120,120,((cgzy_intime_rate-cgzy_target) * (120-100) / (cgzy_challenge-cgzy_target) +100))
             when cgzy_intime_rate>=cgzy_challenge then 120
        end,2)   as cgzy_intime_score
       ,cgzy_weights        as cgzy_intime_weight
       
          
       ,round(case when ks_comp_rate>=ksl_worst then 0 
             when ks_comp_rate<ksl_worst and ks_comp_rate>ksl_target 
			 then if(((ksl_worst-ks_comp_rate) / (ksl_worst-ksl_target) * 100)>100,100,((ksl_worst-ks_comp_rate) / (ksl_worst-ksl_target) * 100))
             when ks_comp_rate=ksl_target then 100
             when ks_comp_rate<ksl_target and ksl_target>ksl_challenge 
			 then if(((ksl_target-ks_comp_rate) * (120-100) / (ksl_target-ksl_challenge) +100)>120,120,((ksl_target-ks_comp_rate) * (120-100) / (ksl_target-ksl_challenge) +100))
             when ks_comp_rate<=ksl_challenge then 120
        end,2)  as ks_comp_score
       ,ksl_weights         as ks_comp_weight
       
       ,round(case when ys_loss_rate>=ysl_worst then 0 
             when ys_loss_rate<ysl_worst and ys_loss_rate>ysl_target 
			 then if(((ysl_worst-ys_loss_rate) / (ysl_worst-ysl_target) * 100)>100,100,((ysl_worst-ys_loss_rate) / (ysl_worst-ysl_target) * 100))
             when ys_loss_rate=ysl_target then 100
             when ys_loss_rate<ysl_target and ysl_target>ysl_challenge 
			 then if(((ysl_target-ys_loss_rate) * (120-100) / (ysl_target-ysl_challenge) +100)>120,120,((ysl_target-ys_loss_rate) * (120-100) / (ysl_target-ysl_challenge) +100))
             when ys_loss_rate<=ysl_challenge then 120
        end,2)   as ys_loss_score
       ,ysl_weights         as ys_loss_weight
       
       ,round(case when ps_worn_rate>=psl_worst then 0 
             when ps_worn_rate<psl_worst and ps_worn_rate>psl_target 
			 then if(((psl_worst-ps_worn_rate) / (psl_worst-psl_target) * 100)>100,100,((psl_worst-ps_worn_rate) / (psl_worst-psl_target) * 100))
             when ps_worn_rate=psl_target then 100
             when ps_worn_rate<psl_target and psl_target>psl_challenge 
			 then if(((psl_target-ps_worn_rate) * (120-100) / (psl_target-psl_challenge) +100)>120,120,((psl_target-ps_worn_rate) * (120-100) / (psl_target-psl_challenge) +100))
             when ps_worn_rate<=psl_challenge then 120
        end,2)   as ps_worn_score
       ,psl_weights         as ps_loss_weight
       
       ,round(case when zcbgf_load_nostandard_cnt<1 then 100
             when zcbgf_load_nostandard_cnt>=1 and zcbgf_load_nostandard_cnt<=3 then (100-(3*zcbgf_load_nostandard_cnt))
             when zcbgf_load_nostandard_cnt>3 then (100-(3*3)-(zcbgf_load_nostandard_cnt-3)*5)
        end,2)   as zcbgf_load_nostandard_score
       ,zcbgf_weights       as zcbgf_load_nostandard_weight 
       
       ,null as cjfl_separated_score
       ,null as cjfl_separated_weight
       
       ,round(case when gx_load_rate<=zfgx_worst then 0 
             when gx_load_rate>zfgx_worst and gx_load_rate<zfgx_target 
			 then if(((gx_load_rate-zfgx_worst) / (zfgx_target-zfgx_worst) * 100)>100,100,((gx_load_rate-zfgx_worst) / (zfgx_target-zfgx_worst) * 100))
             when gx_load_rate=zfgx_target then 100
             when gx_load_rate>zfgx_target and zfgx_target<zfgx_challenge 
			 then if(((gx_load_rate-zfgx_target) * (120-100) / (zfgx_challenge-zfgx_target) +100)>120,120,((gx_load_rate-zfgx_target) * (120-100) / (zfgx_challenge-zfgx_target) +100))
             when gx_load_rate>=zfgx_challenge then 120
        end,2)  as gx_load_score
       ,zfgx_weights       as gx_load_weight
       ,round(case when czxn_eff_reach_rate>=120 then 120
             when czxn_eff_reach_rate>=80 and czxn_eff_reach_rate<120 then czxn_eff_reach_rate
             when czxn_eff_reach_rate<80 then 0 
        end,2)  as czxn_eff_reach_score
       ,czxn_weights       as czxn_eff_reach_weight
       
       ,round(case when dp_per_cost_reach_rate>=120 then 120
             when dp_per_cost_reach_rate>=80 and dp_per_cost_reach_rate<120 then dp_per_cost_reach_rate
             when dp_per_cost_reach_rate<80 then 0 
        end,2)  as dp_per_cost_reach_score
       ,dprl_weights       as dp_per_cost_reach_weight
       ,a.dt
  from (
select  t.stat_date         as stat_date
       ,t.network_code      as center_code            
       ,t.network_name      as center_name            
       ,jgzy_ovtime_cnt   as jgzy_ovtime_cnt        
       ,jgzy_intime_cnt   as jgzy_intime_cnt        
       ,nvl(jgzy_intime_rate,0)  as jgzy_intime_rate     
       ,cgzy_ovtime_cnt   as cgzy_ovtime_cnt      
       ,cgzy_intime_cnt   as cgzy_intime_cnt      
       ,nvl(cgzy_intime_rate,0)  as cgzy_intime_rate     
       ,ks_comp_cnt       as ks_comp_cnt          
       ,nvl(ks_comp_rate,0)      as ks_comp_rate         
       ,ys_loss_cnt       as ys_loss_cnt          
       ,nvl(ys_loss_rate,0)      as ys_loss_rate
       ,ps_worn_cnt       as ps_worn_cnt
       ,nvl(ps_worn_rate,0)     as ps_worn_rate
       ,zx_opt_cnt        as zx_opt_cnt
       ,nvl(zcbgf_load_nostandard_cnt,0)       as zcbgf_load_nostandard_cnt
       ,zz_load_cnt       as zz_load_cnt
       ,hz_load_cnt       as hz_load_cnt
       ,zz_load_kg        as zz_load_kg
       ,hz_load_kg        as hz_load_kg
       ,nvl(gx_load_cnt_rate,0)  as gx_load_cnt_rate
       ,nvl(gx_load_kg_rate,0)   as gx_load_kg_rate
       ,case when nvl(gx_load_cnt_rate,0) >nvl(gx_load_kg_rate,0) then gx_load_cnt_rate else gx_load_kg_rate end            as gx_load_rate            
       ,nvl(t2.cjfl_separated_cnt,0)                                                        as cjfl_separated_cnt
       ,nvl(sum(t2.cjfl_separated_cnt) over(),0)                                            as cjfl_separated_all_cnt       
       ,round((t2.cjfl_separated_cnt/sum(t2.cjfl_separated_cnt) over()*100),2)              as cjfl_separated_rate
       ,nvl(t2.cjfl_separated_abnormal_cnt,0)                                               as cjfl_separated_abnormal_cnt
       ,t3.operate_num                                    as czxn_eff_trans_cnt
       ,(t3.self_attendance+hourly_workers+epiboly_num)   as czxn_eff_attend_num
       ,t3.czxn_eff_reach_rate                            as czxn_eff_reach_rate
       ,0   as dp_per_cost_reach_rate
	   ,dt 
  from (
SELECT trunc(STAT_DATE,'MM') as STAT_DATE
       ,NETWORK_CODE
       ,NETWORK_NAME
       ,sum(CASE WHEN KPI_CODE='bis00000279' THEN (KPI_FM-KPI_FZ) ELSE 0 END)   AS jgzy_ovtime_cnt             ---进港转运不及时票数
       ,sum(CASE WHEN KPI_CODE='bis00000279' THEN (KPI_FZ) ELSE 0 END)          AS jgzy_intime_cnt             ---进港转运不及时票数       
       ,round(SUM(CASE WHEN KPI_CODE='bis00000279' THEN KPI_FZ ELSE NULL end)/SUM(CASE WHEN KPI_CODE='bis00000279' THEN KPI_FM ELSE NULL END)*100,2)           AS jgzy_intime_rate            ---进港转运及时率       
       ,sum(CASE WHEN KPI_CODE='bis00000153' THEN (KPI_FM-KPI_FZ) ELSE 0 END)   AS cgzy_ovtime_cnt             ---出港转运不及时票数
       ,sum(CASE WHEN KPI_CODE='bis00000153' THEN (KPI_FZ) ELSE 0 END)          AS cgzy_intime_cnt             ---出港转运及时票数       
       ,round(SUM(CASE WHEN KPI_CODE='bis00000153' THEN KPI_FZ ELSE NULL end)/SUM(CASE WHEN KPI_CODE='bis00000153' THEN KPI_FM ELSE NULL END)*100,2)           AS cgzy_intime_rate            ---出港转运及时率
       ,sum(CASE WHEN KPI_CODE='sqs00000010' THEN (KPI_FZ) ELSE 0 END)          as ks_comp_cnt   ---客诉量
       ,round(sum(CASE WHEN KPI_CODE='sqs00000010' then KPI_FZ ELSE NULL END)/sum(CASE WHEN KPI_CODE='ops00000003' then KPI_FZ ELSE NULL END)*2.15*10000,2)    AS ks_comp_rate  ---客诉率
       ,sum(CASE WHEN KPI_CODE='sqs00000036' THEN (KPI_FZ) ELSE 0 END)          as ys_loss_cnt   ---遗失量
       ,round(sum(CASE WHEN KPI_CODE='sqs00000036' then KPI_FZ ELSE NULL END)/sum(CASE WHEN KPI_CODE='ops00000003' then KPI_FZ ELSE NULL END)*1000000,2)       AS ys_loss_rate  ---遗失率       
       ,sum(CASE WHEN KPI_CODE='sqs00000008' THEN (KPI_FZ) ELSE 0 END)          as ps_worn_cnt   ---破损量
       ,round(sum(CASE WHEN KPI_CODE='sqs00000008' then KPI_FZ ELSE NULL END)/sum(CASE WHEN KPI_CODE='ops00000003' then KPI_FZ ELSE NULL END)*1000000,2)       AS ps_worn_rate  ---破损率       
       ,sum(CASE WHEN KPI_CODE='ops00000003' THEN (KPI_FZ) ELSE 0 END)          as zx_opt_cnt    ---操作量
       ,SUM(CASE WHEN KPI_CODE='sqs0000000054' THEN KPI_FZ ELSE NULL END)       AS zcbgf_load_nostandard_cnt   ---装车不规范次数
       ,sum(CASE WHEN KPI_CODE='pps00000016' then KPI_FZ ELSE NULL END)         as zz_load_cnt
       ,sum(CASE WHEN KPI_CODE='bis0000000506' then KPI_FZ ELSE NULL END)       as hz_load_cnt
       ,sum(CASE WHEN KPI_CODE='pps00000008' then KPI_FZ ELSE NULL END)         as zz_load_kg
       ,sum(CASE WHEN KPI_CODE='bis0000000507' then KPI_FZ ELSE NULL END)       as hz_load_kg
       ,round(sum(CASE WHEN KPI_CODE='pps00000016' then KPI_FZ ELSE NULL END)/sum(CASE WHEN KPI_CODE='bis0000000506' then KPI_FZ ELSE NULL END)*100,2)  AS gx_load_cnt_rate ---干线装载率(票数)
       ,round(sum(CASE WHEN KPI_CODE='pps00000008' then KPI_FZ ELSE NULL END)/sum(CASE WHEN KPI_CODE='bis0000000507' then KPI_FZ ELSE NULL END)*100,2)  AS gx_load_kg_rate  ---干线装载率(重量)
    ---单票人力成本        达成率 满分120分，达成率≥120%，得分为120分；达成率＜80%，得分为0分；80%＜达成率＜120%，得分为达成率；
    ---操作效能【此处为权重】 达成率 达成率=转运货量/中心操作出勤人数合计（原报表有转运货量值）       
	   ,trunc(STAT_DATE,'MM')   as  dt 
  FROM jms_dwm.dwm_analysis_target_network_sum_day_dt
 WHERE DT>=trunc(add_months('{{ execution_date | cst_ds }}',-1),'MM')  AND DT<='{{ execution_date | cst_ds }}' ---上月+当月
   AND NETWORK_TYPE='4'
 GROUP BY NETWORK_CODE ,NETWORK_NAME,trunc(STAT_DATE,'MM')
 ) t
 left join separated_abnormal_tab  t2 
   on t.NETWORK_CODE=t2.NETWORK_CODE
  and t.STAT_DATE=t2.STAT_DATE
 left join opt_efficiency_tab t3
   on t.NETWORK_CODE=t3.center_code
  and t.STAT_DATE=t3.m_day_dt
 ) a
  left join (select *
               from jms_dim.dim_center_indicator_config_base_dt 
			  where dt='{{ execution_date | cst_ds }}' and status='Y'
			 )  b 
    on 1=1
  left join jms_dim.dim_network_whole_massage c 
    on a.center_code=c.code   
distribute by dt
 ;


